package com.bridgeintelligent.tag.mapper;

import com.bridgeintelligent.tag.webserver.dim.mgmt.api.vo.DimAssessVO;
import com.bridgeintelligent.tag.webserver.dim.mgmt.service.datamonitoring.model.Datamonitoring;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
/**
 * @description 标签数据监控
 * @author: xiayan 2020-10-13
 **/
@Mapper
public interface DatamonitoringMapper {
    @Select("select isrecommended,dim_id,dim_name,(select d_name from DIM_CLASSIFY where id=classify) as classify," +
            "(select d_name from DIM_DATA_SOURCE where id=data_source) as data_source,show_type,definition,caliber," +
            "scenarios,(select d_name from DIM_DEPARTMENT where id=business_department) as business_department," +
            "business_leader,upt_time,effective_time,dim_large_type,approve_date from  DIM_DATA where dim_id = " +
            "#{dimId}")
    DimAssessVO.DimBaseInfo findDimBaseInfo(String dimId);
    @Select("select YC_PERCENT,KZ_PERCENT,BHD_PERCENT from  DIM_FREE_BQZL " +
            "where col_name = #{dimId} order by update_date desc")
    List<DimAssessVO.DimQualityInfo> findDimFreeQualityInfo(String dimId);
    @Select("select YC_PERCENT,KZ_PERCENT,BHD_PERCENT  from  fct_p_cust_bqpjtj_summary " +
            "where col_name = #{dimId} order by update_date desc")
    List<DimAssessVO.DimQualityInfo> findDimQualityInfo(String dimId);
    @Select("select id,day_type,combina_count,customer_count,search_count,dowload_count,update_date from  " +
            "fct_p_cust_bqpjtj_sycs " +
            "where DIM_ID = #{dimId} and IS_USED = '1' and DAY_TYPE='5' order by day_type asc")
    List<DimAssessVO.DimUseInfo> findDimUseInfo(String dimId);

    @Select("select stat_count  as star ,count(*) as fcount from  DIM_ASSESS_INFO " +
            "where DIM_ID = #{dimId}  and STAT_COUNT in ('20','40','60','80','100') GROUP BY STAT_COUNT ")
    List<Datamonitoring>getStatcount(String dimId);


    @Select("select MORE_TITLE as code from  DIM_ASSESS_INFO " +
            "where DIM_ID = #{dimId} and STAT_COUNT=#{star} GROUP BY MORE_TITLE ")
    List<Datamonitoring> getComment(String dimId,String star);

    @Select("select t1.ftime , nvl(t.c, 0) as fcount from (select to_char(sysdate, 'yyyy') || '-' || lpad(level, 2, 0) as ftime  from dual " +
            "connect by level < 13  union all "+
            " select to_char(add_months(sysdate, -12), 'yyyy') || '-' || lpad(level, 2, 0) as ftime from dual connect by level < 13) t1 "+
            " left join (select substr(t.ftime,1,7)as ftime , count(*) as c   from (select  r.dim_name, r.DAY_TIME ftime     "+
            " from FCT_P_CUST_BQPJTJ_SYCS_DETAILS r  where  r.UPDATE_DATE = #{nowTime} and r.DIM_ID= #{dimId}  and r.ZQ_FLAG='4' AND r.DAY_TIME >= #{startTime} and r.DAY_TIME <= #{endTime} ) t "+
            " group by substr(t.ftime,1,7)) t on t1.ftime = SUBSTR(t.ftime,1,7)   "+
            " where t1.ftime >= #{startTime} and t1.ftime <= #{endTime} ORDER BY ftime desc ")
    List<Datamonitoring> getHistory(String dimId, String startTime, String endTime,String nowTime);

    @Select("select t1.stime , nvl(t.c, 0) as scount from (select to_char(sysdate, 'yyyy') || '-' || lpad(level, 2, 0) as stime  from dual " +
            " connect by level < 13  union all "+
            "  select to_char(add_months(sysdate, -12), 'yyyy') || '-' || lpad(level, 2, 0) as stime from dual connect by level < 13) t1 "+
            " left join (select substr(t.stime,1,7)as stime , count(*) as c   from (select  r.dim_name, r.DAY_TIME stime    "+
            " from FCT_P_CUST_BQPJTJ_SYCS_DETAILS r  where r.UPDATE_DATE = #{nowTime} and r.DIM_ID=#{dimId} and (r.ZQ_FLAG='1' or r.ZQ_FLAG='2')  AND r.DAY_TIME >= #{startTime} and r.DAY_TIME <= #{endTime} ) t  "+
            "   group by substr(t.stime,1,7)) t on t1.stime = SUBSTR(t.stime,1,7)   "+
            " where t1.stime >= #{startTime}  and t1.stime <= #{endTime} ORDER BY stime desc   ")
    List<Datamonitoring> getHistoryxz(String dimId, String startTime, String endTime,String nowTime);

    @Select("select ASSESS_DES as detailed from DIM_ASSESS_INFO   where   dim_id=#{dimId} and STAT_COUNT=#{star}  ")
    List<Datamonitoring> getDetailed(String dimId,String star);
    @Select("select d_name as gzname from dim_show_rule where id=#{showType} ")
    String findShowRule(String showType);

    @Select("select data_source as dataSource FROM DIM_DATA WHERE DIM_ID=#{dimId} ")
   String findDateSource(String dimId);
}
